﻿Imports MySql.Data.MySqlClient
Imports System.IO
Imports System.Net
Imports System.Xml
Imports System.Web.Services.Protocols
Imports DevExpress.XtraGrid.Views.Grid

Public Class frmOPDrug
    Dim DA As New MySqlDataAdapter
    Dim MyConn As New MySqlConnection
    Dim mysql As New ClsSQLhelper
    Dim DSOPPP As New OPPP
    Dim Strsql As String
    Dim Curr_View As String = "Default"
    Dim On_load As Boolean = True
    Dim StrMainsql As String = "select m_DRUG.PID,m_person.CID,m_person.HN,Concat(m_person.NAME,'  ',m_person.LNAME)" & _
           " as NAME,m_DRUG.SEQ,m_DRUG.CLINIC,m_DRUG.DATE_SERV,m_DRUG.DNAME,m_DRUG.DIDSTD,m_DRUG.AMOUNT,m_DRUG.DRUGPRIC,m_DRUG.DRUGCOST ,m_DRUG.CODE_ID  " & _
           "  from m_DRUG left join m_person on  m_DRUG.PID = m_person.PID  "
#Region "Function"
    Sub Search_Information()
        Try
            If Me.VGR_PID.Properties.Value = "000000" Then
                MsgBox("ไม่สามารถหาความเชื่อมโยงได้เนื่องจาก PID = 000000")
                Exit Sub
            End If
            Select Case Me.VGR_CID.Properties.Value
                Case "0000000000000"
                    If MsgBox("รหัสบัตรไม่ถูกกต้อง คุณยืนยันที่จะค้นหาต่อหรือไม่", MsgBoxStyle.Exclamation + MsgBoxStyle.YesNo, "คำเตือน") = MsgBoxResult.No Then Exit Sub
                    frmSearchInfo.TxtCriteria.Text = Me.VGR_PID.Properties.Value
                Case ""
                    If Me.VGR_HN.Properties.Value <> "ไม่ระบุ" Then

                        frmSearchInfo.TxtCriteria.Text = Me.VGR_HN.Properties.Value
                    Else
                        If Me.VGR_PID.Properties.Value <> "" Then
                            frmSearchInfo.TxtCriteria.Text = Me.VGR_PID.Properties.Value
                        ElseIf Me.VGR_Fullname.Properties.Value <> "" Then
                            frmSearchInfo.TxtCriteria.Text = Me.VGR_Fullname.Properties.Value
                        Else

                            MsgBox("ไม่สามารถ หาความเชื่อมโยงได้", MsgBoxStyle.Critical)
                        End If

                    End If
                Case Else
                    frmSearchInfo.TxtCriteria.Text = Me.VGR_CID.Properties.Value
            End Select
            With frmSearchInfo
                .TxtYear.Text = Me.TxtYear.Text
                .TxtMonth.Text = Me.TxtMonth.Text
                '.TxtDateServ.Text = Me.TxtDateServ.Text
                .Submit_Search()
                .ShowDialog()
            End With
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
    Sub GenScript()
        Try
            Dim Strsql As String = " CID in ("
            Dim HNSql As String = " HN in ("
            Dim PIDsql As String = " person_id in ("
            Select Case Me.Curr_View

                Case "OP9200"

                    For i As Integer = 0 To DataGridView1.RowCount - 1

                        Strsql = Strsql & "'" & DataGridView1.Rows(i).Cells("CID").Value.ToString.Trim() & "',"
                        HNSql = HNSql & "'" & DataGridView1.Rows(i).Cells("HN").Value.ToString.Trim() & "',"
                        PIDsql = PIDsql & "'" & DataGridView1.Rows(i).Cells("PID").Value.ToString.Trim() & "',"
                    Next
                    Strsql = Mid(Strsql, 1, Len(Strsql) - 1) & ")"
                    HNSql = Mid(HNSql, 1, Len(HNSql) - 1) & ")"
                    PIDsql = Mid(PIDsql, 1, Len(PIDsql) - 1) & ")"
                    SQLDialog.Sqltext.Text = Strsql
                    SQLDialog.Sqltext2.Text = HNSql
                    SQLDialog.Sqltext3.Text = PIDsql

                    SQLDialog.ShowDialog()

                Case "Default"

            End Select

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
    Sub Load_Summary()
        Try
            Strsql = "select CODE_ID,Count(CODE_ID) as Totalrows from m_drug where filename = " & CboImportOPFile.SelectedValue & " and code_id is not null  group by code_id "
            Dim DT_Service As New DataTable
            DT_Service = mysql.GetMYSQLDataTable(Strsql, "ERROR_Summary")

            With Grid_ErrorCode
                .DataSource = DT_Service

            End With
            With DataGridView1

            End With
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
    Sub Load_Data(ByVal Code_ID As String, Optional ByVal StrAddonSql As String = "")
        Try
            Me.Cursor = Cursors.WaitCursor
            Me.Curr_View = Code_ID
            'DSOPPP.Tables("m_person").Clear()
            'Strsql = "Select m_person.PCUCODE,m_person.PID,m_person.HN,m_person.CID,Concat(m_person.NAME,'  ',m_person.LNAME) as NAME,m_person.SEX,m_person.BIRTH,m_person.MSTATUS,m_person.OCCUPA,m_person.NATION,m_person.TYPEAREA,m_person.CODE_ID from m_person where status = 1 and code_id Like '%OP9200%' and FILENAME = " & Me.CboImportOPFile.SelectedValue
            '  Strsql = "select m_service.PCUCODE,m_service.PID,m_person.CID,m_person.HN,Concat(m_person.NAME,'  ',m_person.LNAME) as NAME,m_person.SEX,m_person.BIRTH,m_service.SEQ,m_service.DATE_SERV,m_service.CLINIC,m_service.PRICE,m_service.PAY,m_service.SERVICE_TYPE,m_service.REFERIN,m_service.REFINHOS,m_service.REFEROUT,m_service.REFOUHOS  from m_service left join m_person on  m_service.PID = m_person.PID  where m_service.CODE_ID  like  '%OP9200%' and m_service.FILENAME = " & Me.CboImportOPFile.SelectedValue & "   order by  m_service.PID"
            Strsql = "select m_DRUG.PID,Concat(m_person.NAME,'  ',m_person.LNAME)" & _
           " as NAME,m_DRUG.SEQ,m_DRUG.CLINIC,m_DRUG.DATE_SERV,m_DRUG.DNAME,m_DRUG.DIDSTD,m_DRUG.AMOUNT,m_DRUG.DRUGPRIC,m_DRUG.DRUGCOST ,m_DRUG.CODE_ID  " & _
           "  from m_DRUG left join m_person on  m_DRUG.PID = m_person.PID  " & _
           " where m_DRUG.CODE_ID  like  '%" & Code_ID & "%'  " & StrAddonSql & "  and m_DRUG.FILENAME = " & Me.CboImportOPFile.SelectedValue

            If Me.ChkShowPIDError.Checked = False Then
                Strsql = Strsql & " and  m_DRUG.pid <> 000000 "
            End If
            If Me.ChkShowNewBorn.Checked = False Then
                Strsql = Strsql & " and  m_person.NAME not like '(%' "
            End If
            Strsql = Strsql & " order by  m_DRUG.PID desc"

            '  DA = New MySqlDataAdapter(Strsql, MyConn)
            ' DA.Fill(DSOPPP, "m_person")
            Dim DT_Person As New DataTable
            DT_Person = mysql.GetMYSQLDataTable(Strsql, "m_DRUG")
            With DataGridView1
                .RowHeadersVisible = False
                .AllowDrop = False
                .AllowUserToAddRows = False
                .AllowUserToDeleteRows = False
                .AllowUserToOrderColumns = True
                .AllowUserToResizeColumns = True
                .AllowUserToResizeRows = False
                .MultiSelect = False
                .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells
                .AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells
                .DataSource = DT_Person

                If Me.DataGridView1.RowCount < 1 Then
                    MsgBox("ไม่พบข้อมูล", MsgBoxStyle.Exclamation)
                Else
                    MsgBox("ดึงข้อมูลเรียบร้อยแล้ว", MsgBoxStyle.Information)
                End If
            End With
            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            MsgBox(ex.Message)
        End Try
    End Sub

#End Region
    Private Sub frmOPDrug_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            ' Me.TxtZoomCID.Text = "0000000000000"
            Dim DT_m_files_upload As DataTable
            Strsql = "select ROWID,FILENAME from m_files_upload "
            Strsql = Strsql & " where TYPE_DESC = 'OP Package' or  TYPE_DESC = '18 แฟ้ม' "
            Strsql = Strsql & " order by Date_SEND DESC"


            With Me.CboImportOPFile

                DT_m_files_upload = mysql.GetMYSQLDataTable(Strsql, "m_files_upload")
                If DT_m_files_upload.Rows.Count = 0 Then
                    MsgBox("คุณต้องนำข้อมูลเข้าโปรแกรมตรวจสอบ NHSO OPPP 2554 ก่อน", MsgBoxStyle.Exclamation)
                    Exit Sub
                End If
                .DataSource = DT_m_files_upload
                .DisplayMember = "FILENAME"
                .ValueMember = "ROWID"
                Me.CboImportOPFile.SelectionStart = DT_m_files_upload.Rows(0).Item("ROWID").ToString

            End With
            If InStr(Me.CboImportOPFile.Text, "FOP", CompareMethod.Text) > 0 Then
                Me.TxtYear.Text = Mid(Me.CboImportOPFile.Text, 11, 4)
                Me.TxtMonth.Text = Mid(Me.CboImportOPFile.Text, 15, 2)
            End If
            If InStr(Me.CboImportOPFile.Text, "E_PCU", CompareMethod.Text) > 0 Then
                'E_PCU_11266_201010_20101202.zip
                Me.TxtYear.Text = Val(Mid(Me.CboImportOPFile.Text, 13, 4)) + 543
                Me.TxtMonth.Text = Mid(Me.CboImportOPFile.Text, 17, 2)
            End If
            Load_Summary()

            Strsql = "Select (((AMOUNT-ERROR)/amount))*100 as point from m_upload where filename = 'DRUG'"
            Dim MyPoint As String
            MyPoint = mysql.MySQLExecuteScalar(Strsql)
            Me.CircularGauge1.Scales(0).Value = Val(MyPoint)
            Me.CircularGauge1.Labels(0).Text = FormatNumber(MyPoint, 2) & " %"

            On_load = False
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        MyConn.ConnectionString = ClsSQLhelper.MySQLConnectionStr
        ' Read_DataQuality()
    End Sub

   


    Private Sub CboImportOPFile_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CboImportOPFile.SelectedIndexChanged
        Try
            If On_load = True Then Exit Sub
            If InStr(Me.CboImportOPFile.Text, "FOP", CompareMethod.Text) > 0 Then
                Me.TxtYear.Text = Mid(Me.CboImportOPFile.Text, 11, 4)
                Me.TxtMonth.Text = Mid(Me.CboImportOPFile.Text, 15, 2)
            End If
            If InStr(Me.CboImportOPFile.Text, "E_PCU", CompareMethod.Text) > 0 Then
                'E_PCU_11266_201010_20101202.zip
                Me.TxtYear.Text = Val(Mid(Me.CboImportOPFile.Text, 13, 4)) + 543
                Me.TxtMonth.Text = Mid(Me.CboImportOPFile.Text, 17, 2)
            End If

            '  Read_DataQuality()
            Load_Summary()
            'Select Case Me.Curr_View
            '    Case "RX9200"
            '        BtnRX9200_Click(sender, e)
            '    Case "RX9299"
            '        BtnRX9299_Click(sender, e)
            '    Case "Rx1130"
            '        BtnRx1130_Click(sender, e)
            '    Case "Rx9230"
            '        BtnRX9230_Click(sender, e)
            'End Select

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

   

  
    Private Sub DataGridView1_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellClick
        Try

            With Me.DataGridView1
                Dim Error_Code As String

                With Me.DataGridView1
                    Dim MyPerson As New ClsPerson
                    MyPerson.Get_NHSO_Profile(.Rows(e.RowIndex).Cells("PID").Value.ToString.Trim)

                    Me.VGR_CID.Properties.Value = MyPerson.CID
                    Me.VGR_PID.Properties.Value = .Rows(e.RowIndex).Cells("PID").Value.ToString.Trim
                    Me.VGR_Fullname.Properties.Value = MyPerson.Get_Person_FullName
                    Me.VGR_HN.Properties.Value = MyPerson.HN
                    Me.VGR_MStatus.Properties.Value = MyPerson.MStatus
                    Me.VGR_FormatCID.Properties.Value = MyPerson.Get_Person_FormatCID
                    Me.VGR_Nationality.Properties.Value = MyPerson.Nation
                    Me.VGR_Occupation.Properties.Value = MyPerson.Ocupaton
                    Me.VGR_Race.Properties.Value = MyPerson.Race
                    Me.VGR_Sex.Properties.Value = MyPerson.Sex
                    Me.VGR_TypeArea.Properties.Value = MyPerson.TypeArea
                    Me.VGA_BirthDay.Properties.Value = MyPerson.BirthDay
                    Me.VGA_AGE.Properties.Value = MyPerson.Age
                    Error_Code = DataGridView1.Rows(e.RowIndex).Cells("CODE_ID").Value

                    If Mid(Error_Code, 1, 1) = "," Then
                        Error_Code = Mid(Error_Code, 2, Error_Code.Length)
                    End If
                    If InStr(Error_Code, ",", CompareMethod.Text) > 0 Then
                        Error_Code = Replace(Error_Code, ",", "','")
                    End If
                    Strsql = "select code_id,remark from l_error_code where code_id in ('" & Error_Code & "')"
                    Dim DT_ERROR As New DataTable
                    DT_ERROR = mysql.GetMYSQLDataTable(Strsql, "l_error_code")
                    With ErrorDesc_Grid
                        .DataSource = DT_ERROR
                    End With
                End With

                '(Me.TXTDSTD.Text = DataGridView1.Rows(e.RowIndex).Cells("DIDSTD").Value.ToString)

                Strsql = "Select concat(drugname,' ',unit) from l_drugs where STD_CODE = '" & DataGridView1.Rows(e.RowIndex).Cells("DIDSTD").Value.ToString & "'"
                VGR_HOSxP_DRUG.Properties.Value = DataGridView1.Rows(e.RowIndex).Cells("DName").Value.ToString
                If VGR_HOSxP_DRUG.Properties.Value = "" Then
                    VGR_HOSxP_DRUG.Properties.Value = "ไม่พบข้อมูล"
                End If
                VGR_NHSO_DRUG.Properties.Value = mysql.MySQLExecuteScalar(Strsql)


                If VGR_NHSO_DRUG.Properties.Value = "" Then
                    VGR_NHSO_DRUG.Properties.Value = "ไม่พบข้อมูล"
                End If
            End With
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub


    Private Sub DataGridView1_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick

    End Sub

    Private Sub DataGridView1_CellDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellDoubleClick
        Search_Information()

    End Sub

    Private Sub DataGridView1_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles DataGridView1.KeyDown
        If e.KeyCode = Keys.F5 Then
            Search_Information()
        End If
    End Sub

 

    Private Sub RBM_GenScript_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles RBM_GenScript.ItemClick
        GenScript()
    End Sub

    Private Sub RBM_FInd_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles RBM_FInd.ItemClick
        Search_Information()

    End Sub

    Private Sub RBM_ExportExcel_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles RBM_ExportExcel.ItemClick
        If Me.DataGridView1.RowCount = 0 Then Exit Sub


        If Me.DataGridView1.RowCount = 0 Then Exit Sub
        If MsgBox("คุณต้องการส่งออกข้อมูลในตาราง ออกเป็นรูปแบบ Excel หรือไม่", MsgBoxStyle.Question + MsgBoxStyle.YesNo, "Export to Excel") = MsgBoxResult.No Then Exit Sub


        With SaveFileDialog1
            .FileName = Me.Text
            .Filter = "Excel 2003 | *.xls"
            If .ShowDialog() <> DialogResult.OK Then Exit Sub

            Dim Str As String = "" ' = "PCUCODE,PID,HN,CID,NAME,SEX,BIRTH,MSTATUS,OCCUPA,NATION,TYPEAREA,CODE_ID"
            For i = 0 To DataGridView1.ColumnCount - 1
                Str = Str & DataGridView1.Columns(i).HeaderText
                If i <> DataGridView1.ColumnCount - 1 Then
                    Str = Str & ","
                End If

            Next
            Using sw As StreamWriter = New StreamWriter(.FileName, False, System.Text.Encoding.UTF8)
                sw.WriteLine(Str)

                '  
                For i As Integer = 0 To DataGridView1.RowCount - 1
                    Str = ""
                    For j As Integer = 0 To DataGridView1.ColumnCount - 1
                        Str = Str & DataGridView1.Rows(i).Cells(j).Value.ToString
                        If j <> DataGridView1.ColumnCount - 1 Then
                            Str = Str & ","
                        End If

                    Next
                    sw.WriteLine(Str)
                Next
                sw.Close()

            End Using


            MsgBox("ส่งออกข้อมูลเรียบร้อยแล้ว", MsgBoxStyle.Information)

        End With
    End Sub

    Private Sub RBM_RX9200_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles RBM_RX9200.ItemClick
        Load_Data("RX9200")
    End Sub

    Private Sub RBM_RX9230_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles RBM_RX9230.ItemClick
        Load_Data("RX9230")
    End Sub

    Private Sub RBM_RX9299_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles RBM_RX9299.ItemClick
        Load_Data("RX9299")
    End Sub

    Private Sub RBM_RX1104_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles RBM_RX1104.ItemClick
        Load_Data("RX1104")
    End Sub

    Private Sub RBM_RX1105_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles RBM_RX1105.ItemClick
        Load_Data("RX1105")
    End Sub

    Private Sub RBM_RX1106_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles RBM_RX1106.ItemClick
        Load_Data("RX1106")
    End Sub

    Private Sub RBM_RX1120_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles RBM_RX1120.ItemClick
        Load_Data("RX1120")
    End Sub

    Private Sub RBM_RX1121_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles RBM_RX1121.ItemClick
        Load_Data("RX1121")
    End Sub

    Private Sub RBM_RX1130_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles RBM_RX1130.ItemClick
        Load_Data("RX1130")
    End Sub

    Private Sub RBM_RX1131_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles RBM_RX1131.ItemClick
        Load_Data("RX1131")
    End Sub

    Private Sub RBM_Drug_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles RBM_Drug.ItemClick
        frmdruginformation.ShowDialog()
    End Sub
    Private Sub GridView2_CustomRowCellEditForEditing(ByVal sender As System.Object, ByVal e As DevExpress.XtraGrid.Views.Grid.CustomRowCellEditEventArgs) Handles GridView2.CustomRowCellEditForEditing
        Try
            'If e.Column.FieldName = "Totalrows" Then Return
            Dim Gv As GridView = sender
            Dim CodeName As String = Gv.GetRowCellValue(e.RowHandle, _
              Gv.Columns("CODE_ID")).ToString()


            Load_Data(CodeName)
        Catch ex As Exception

        End Try
    End Sub
End Class